﻿CREATE PROCEDURE [dbo].[books_insert]
    @dateOfPublish date,
    @shortContent nvarchar(255),
    @title nvarchar(256),
    @avatar nvarchar(256),
    @fileName nvarchar(256),
    @userId int,
    @authors list_author readonly,
    @categories list_category readonly
AS
INSERT INTO [dbo].[Books]
(    DateOfPublish,
    ShortContent,
    Title,
    Avatar,
    FileName,
    UserId)
VALUES(
    @dateOfPublish,
    @shortContent,
    @title,
    @avatar,
    @fileName,
    @userId)
DECLARE @bookId int = @@IDENTITY

-- insert all authors
DECLARE @authorId int
DECLARE @get_authors CURSOR

SET @get_authors = CURSOR FOR
     SELECT AuthorId FROM @authors

OPEN @get_authors
 
FETCH NEXT FROM @get_authors INTO @authorId

WHILE (@@FETCH_STATUS = 0) 
BEGIN
     INSERT INTO [dbo].[BooksByAuthor](BookId,AuthorId) VALUES (@bookId, @authorId)
     FETCH NEXT FROM @get_authors INTO @authorId
END

CLOSE @get_authors
-- end insert all authors

-- insert all categories
DECLARE @categoryId int
DECLARE @get_categories CURSOR

SET @get_categories = CURSOR FOR
     SELECT  CategoryId FROM @categories

OPEN @get_categories
 
FETCH NEXT FROM @get_categories INTO @categoryId

WHILE (@@FETCH_STATUS = 0) 
BEGIN
     INSERT INTO [dbo].[BooksByCategory](BookId,CategoryId) VALUES (@bookId,  @categoryId)
     FETCH NEXT FROM @get_categories INTO @categoryId
END

CLOSE @get_categories
-- end insert all categories

SELECT
    [BookId],
    [DateOfPublish],
    [ShortContent],
    [Title],
    [Avatar],
    [FileName],
    [UserId],
   (SELECT DISTINCT CAST(BooksByAuthor.AuthorId as nvarchar) + ',' AS 'data()'
                     FROM   BooksByAuthor
                     WHERE  BooksByAuthor.BookId = @bookId FOR xml path('')) AS authors,
   (SELECT DISTINCT CAST(BooksByCategory.CategoryId as nvarchar) + ',' AS 'data()'
                     FROM   BooksByCategory
                     WHERE  BooksByCategory.BookId = @bookId FOR xml path('')) AS categories
FROM [dbo].[Books] 
WHERE
    Books.[BookId]=@bookId

RETURN 0
